﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FindArbitrageConsole.Data.Model;
using MySql.Data.MySqlClient;

namespace FindArbitrageConsole
{
    class DbConnect : IDbConnect
    {
        private MySqlConnection Connection { get; set; }

        public DbConnect()
        {
            var connectionString = "SERVER=localhost;DATABASE=kef_stats;UID=root;PASSWORD=Sql2008";
            Connection = new MySqlConnection(connectionString);
        }

        public IEnumerable<KefData> GetAllMaxKefs(Int32? byEventId = null)
        {
            string query = "SELECT * FROM kef_max";
            if (byEventId != null)
                query += string.Format(" WHERE event_id = {0}",byEventId);

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }

        public List<KefData> GetAllKefs()
        {
            string query = "SELECT * FROM kef";

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }

        public IEnumerable<KefData> GetArbMarkets(Int32? byEventId = null)
        {
            string query = "SELECT * FROM arbMarketWithKef";
            if (byEventId != null)
                query += string.Format(" WHERE event_id = {0}",byEventId);

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }

        public IEnumerable<int> GetAllEvents()
        {
            string query = "SELECT event_id FROM kef GROUP BY event_id";

            var listEvents = new List<int>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    listEvents.Add(Convert.ToInt32(dataReader["event_id"]));
                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listEvents;
        }

        public IEnumerable<ArbitrageLinkData> GetArbitrageLinks(Int32? byEventId = null)
        {
            string query = "SELECT arbitrage_id,kef_id FROM arbitrage_link";
            string queryByEvent =string.Format("SELECT arbitrage_id,kef_id from arbitrage_link inner join kef on kef_id = kef.id WHERE kef.event_id = {0}",byEventId);
            if (byEventId != null)
                query = queryByEvent;

            var listArbLinks = new List<ArbitrageLinkData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var arbitrageId = Convert.ToInt32(dataReader["arbitrage_id"]);
                    var kefId = Convert.ToInt32(dataReader["kef_id"]);

                    var kefData = new ArbitrageLinkData(){ ArbitrageId = arbitrageId,KefId = kefId};
                    listArbLinks.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listArbLinks;
        }

        public void WriteArbitrages(List<IEnumerable<ArbitrageLinkData>> listArbitrages)
        {
            if (listArbitrages == null || !listArbitrages.Any())
                return;

            foreach (var arb in listArbitrages)
            {
                var arbId = CreateArbitrage("empty");
                arb.ToList().ForEach(l => l.ArbitrageId = arbId.Value);
                WriteArbitrage(arb);
            }

        }

        public List<KefData> GetKefs(IEnumerable<Tuple<int,int,int>> eventMarketSelectionIds)
        {
            const string insertQueryHead = "SELECT * FROM kef WHERE ";

            var listValues = eventMarketSelectionIds.Select(kefInfo => (string.Format("(event_id = {0} AND market_id = {1} AND selection_id = {2})", kefInfo.Item1, kefInfo.Item2, kefInfo.Item3))).Distinct();

            var completeQuery = insertQueryHead + string.Join(" OR ", listValues);

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = completeQuery;
                var reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    var id = Convert.ToInt32(reader["id"]);
                    var event_id = Convert.ToInt32(reader["event_id"]);
                    var market_id = Convert.ToInt32(reader["market_id"]);
                    var sel_id = Convert.ToInt32(reader["selection_id"]);
                    var kef_value = Convert.ToDecimal(reader["kef_value"]);
                    var bookm_id = Convert.ToInt32(reader["bookmaker_id"]);
                    var time = Convert.ToDateTime(reader["update_time"]);

                    listKefs.Add(new KefData(id,event_id,market_id,sel_id,bookm_id,kef_value,time));
                }
                reader.Close();

                this.CloseConnection();
            }

            return listKefs;
        }

        #region Private Funcs

        private void WriteArbitrage(IEnumerable<ArbitrageLinkData> listLinks)
        {
            if (listLinks == null || !listLinks.Any())
                return;

            const string insertQueryHead = "INSERT INTO arbitrage_link(arbitrage_id,kef_id) VALUES ";

            var listValues = listLinks.Select(link => (string.Format(" ({0},{1}) ", link.ArbitrageId, link.KefId)));

            var completeQuery = insertQueryHead + string.Join(",", listValues);

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = completeQuery;
                cmd.ExecuteNonQuery();

                this.CloseConnection();
            }
        }
        
        private Int32? CreateArbitrage(string someData)
        {
            string insertQuery = string.Format("INSERT INTO arbitrage(some_info) values('{0}')", someData);
            string queryGetId = string.Format("SELECT MAX(id) FROM arbitrage");

            Int32? arbId = null;

            if (this.OpenConnection() == true)
            {

                var cmdInsert = Connection.CreateCommand();
                cmdInsert.CommandText = insertQuery;
                cmdInsert.ExecuteNonQuery();

                var cmdGetId = Connection.CreateCommand();
                cmdGetId.CommandText = queryGetId;
                arbId = Convert.ToInt32(cmdGetId.ExecuteScalar());

                this.CloseConnection();
            }

            return arbId;
        }

        #endregion

        #region Private Funcs Connection
        private bool OpenConnection()
        {
            try
            {
                Connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.

                /*
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                */
                return false;
            }
        }
        private bool CloseConnection()
        {
            try
            {
                Connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message);
                return false;
            }
        }
        #endregion
    }
}
